﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace PurchaseManagementSystem
{
    class DBHelper
    {
        private static SqlConnection connection;
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public static SqlConnection Connection()
        {
            {
                //string connectionString = ConfigurationSettings.AppSettings["PurchaseManagementSystemEntities"].ToString(); 
                //string connectionString = ConfigurationManager.ConnectionStrings["PurchaseManagementSystemEntities"].ConnectionString;
               // string connectionString = @"Data Source=.\sqlExpress;Database=PurchaseManagementSystem;Uid=sa;Pwd=sa;Trusted_Connection=true;Persist Security Info=True";
               // string connectionString = @"Data Source=WANGJIA_PC\SQLEXPRESS;Database=kcsj;integrated security=true";
               //string connectionString = @"Data Source=.\sqlExpress;Database=PurchaseManagementSystem;integrated security=true";
                string connectionString = "Data Source=.;Database=kcsj;integrated security=true";


                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                else {
                }
               
            }
             return connection;
        }
        /// <summary>
        /// 执行并返回影响行数
        /// </summary>
        /// <param name="safeSql">SQL语句</param>
        /// <returns></returns>
        public static int ExecuteCommand(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection());
            int result = cmd.ExecuteNonQuery();
            return result;
        }
        /// <summary>
        /// 执行并返回影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection());
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 执行并返回执行结果中的第一列
        /// </summary>
        /// <param name="safeSql">SQL语句</param>
        /// <returns>还回int类型</returns>
        public static int GetScalar(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection());
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }
        /// <summary>
        /// 执行并返回执行结果中的第一列
        /// </summary>
        /// <param name="safeSql">SQL语句</param>
        /// <returns>还回string类型</returns>
        public static string GetScalarString(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection());
            string result = Convert.ToString(cmd.ExecuteScalar());
            return result;
        }
        /// <summary>
        /// 执行并返回执行结果中的第一列
        /// </summary>
        /// <param name="safeSql">SQL语句</param>
        /// <returns>返回DateTime类型</returns>
        public static DateTime GetScalarDateTime(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection());
            return Convert.ToDateTime(cmd.ExecuteScalar());
        }

        /// <summary>
        /// 执行并返回执行结果中的第一列
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="values"></param>
        /// <returns>返回DateTime类型</returns>
        public static DateTime GetScalarDateTime(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection());
            cmd.Parameters.AddRange(values);
            DateTime result = (DateTime)cmd.ExecuteScalar();
            return result;
        }

        /// <summary>
        /// 判断是否有搜索结果__王佳
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        //public static bool Exists(string strSql)
        //{
        //    object obj = DBHelper.GetSingle(strSql);
        //    int cmdresult;
        //    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
        //    {
        //        cmdresult = 0;
        //    }
        //    else
        //    {
        //        cmdresult = int.Parse(obj.ToString());
        //    }
        //    if (cmdresult == 0)
        //    {
        //        return false;
        //    }
        //    else
        //    {
        //        return true;
        //    }
        //}

        //public static object GetSingle(string SQLString)
        //{
        //    using (connection)
        //    {
        //        using (SqlCommand cmd = new SqlCommand(SQLString, connection))
        //        {
        //            try
        //            {
        //                connection.Open();
        //                object obj = cmd.ExecuteScalar();
        //                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
        //                {
        //                    return null;
        //                }
        //                else
        //                {
        //                    return obj;
        //                }
                        
        //            }
        //            catch (System.Data.SqlClient.SqlException e)
        //            {
        //                connection.Close();
        //                throw e;
        //            }
        //        }
        //    }
        //}


        /// <summary>
        /// 根据sql语句获得sqldatareader
        /// </summary>
        /// <param name="safeSql">SQL语句</param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection());
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        /// <summary>
        /// 根据sql语句获得DataTable
        /// </summary>
        /// <param name="safeSql">SQL语句</param>
        /// <returns></returns>
        public static DataTable GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection());
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }
        /// <summary>
        /// 执行并返回执行结果中的第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static int GetScalar(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection());
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }
        /// <summary>
        /// 执行并返回执行结果中的第一列
        /// </summary>
        /// <param name="safeSql">SQL语句</param>
        /// <returns>还回string类型</returns>
        public static string GetScalarString(string safeSql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection());
            cmd.Parameters.AddRange(values);
            string result = Convert.ToString(cmd.ExecuteScalar());
            return result;
        }
        /// <summary>
        /// 根据sql语句获得sqldatareader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection());
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        /// <summary>
        /// 利用存储过程，根据sql语句获得DataTable
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static DataTable GetDataSet(string sql, params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, Connection());
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }
    }
}
